SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[GD] AS 
    SELECT  GRNDetails.DrugId ,
            SUM(GRNDetails.Quantity) AS Quantity ,
            GRNDetails.ExpDate ,
            GRNDetails.BatchLot
    FROM    GRNDetails
            INNER JOIN GRN ON GRNDetails.GRNId = GRN.GRNId
            LEFT OUTER JOIN PD ON GRNDetails.DrugId = PD.DrugId
                                  AND GRNDetails.ExpDate = PD.ExpDate
                                  AND GRNDetails.BatchLot = PD.BatchLot
    WHERE   ( GRN.IsComplete = 1 )
    GROUP BY GRNDetails.DrugId ,
            GRNDetails.ExpDate ,
            GRNDetails.BatchLot
GO
